package com.ericsson.rm.dao.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.ericsson.rm.model.ProjectResourceDetail;

public interface ProjectResourceDetailDaoRepository extends JpaRepository<ProjectResourceDetail, String> {
		
	@Query("select PR.resourceDetail.resourceId, PR.resourceDetail.firstName, PR.resourceDetail.lastName, PR.resourceDetail.signum, " +
			"PR.resourceDetail.lineManagerSignum, PR.resourceDetail.roleDetail.jobStage, PR.resourceDetail.roleDetail.roleName, " +
			"SUM(PR.allocationPercentage) as totalAllocationPercentage " +
			"from ProjectResourceDetail PR " +
			"where PR.allocationStartDate <= to_date(:fromDate, 'DD/MM/YYYY') and PR.allocationEndDate >= to_date(:fromDate, 'DD/MM/YYYY') " +
			"group by PR.resourceDetail.resourceId, PR.resourceDetail.firstName, PR.resourceDetail.lastName, PR.resourceDetail.signum, PR.resourceDetail.lineManagerSignum, PR.resourceDetail.roleDetail.jobStage, PR.resourceDetail.roleDetail.roleName ")
	List<Object[]> searchResourceAllocation(@Param("fromDate") String fromDate);
	
	@Query("select PR.resourceDetail.resourceId, PR.resourceDetail.firstName, PR.resourceDetail.lastName, PR.resourceDetail.signum, " +
			"PR.resourceDetail.lineManagerSignum, PR.resourceDetail.roleDetail.jobStage, PR.resourceDetail.roleDetail.roleName, " +
			"SUM(PR.allocationPercentage) as totalAllocationPercentage " +
			"from ProjectResourceDetail PR " +
			"where PR.resourceDetail.roleDetail.jobStage=:jobStage " +
			"and PR.allocationStartDate <= to_date(:fromDate, 'DD/MM/YYYY') and PR.allocationEndDate >= to_date(:fromDate, 'DD/MM/YYYY') " +
			"group by PR.resourceDetail.resourceId, PR.resourceDetail.firstName, PR.resourceDetail.lastName, PR.resourceDetail.signum, PR.resourceDetail.lineManagerSignum, PR.resourceDetail.roleDetail.jobStage, PR.resourceDetail.roleDetail.roleName ")
	List<Object[]> searchResourceAllocation(@Param("fromDate") String fromDate, @Param("jobStage") String jobStage);
	
	//@Query("select PR.resourceDetail.resourceId, SUM(PR.allocationPercentage) as totalAllocationPercentage from ProjectResourceDetail PR where PR.resourceDetail.resourceSkillDetails.skillDetail.skillId=:skillId and PR.resourceDetail.roleDetail.jobStage=:jobStage and PR.allocationStartDate <= to_date(:fromDate, 'DD/MM/YYYY') and PR.allocationEndDate >= to_date(:fromDate, 'DD/MM/YYYY') group by PR.resourceDetail.resourceId")
	//List<Object[]> searchResourceAllocation(@Param("fromDate") String fromDate, @Param("jobStage") String jobStage, @Param("skillId") String skillId);

	@Query(value = "select resource_id, event_date, flag, allocation_percentage as allocation_change from " +
			"( select resource_id, allocation_start_date event_date, 'START' flag, allocation_percentage  from RP_PROJECT_RESOURCE_DETAIL " +
			"where resource_id in :resourceIdList and " +
			"allocation_start_date > to_date(:fromDate, 'DD/MM/YYYY') and allocation_start_date < to_date(:toDate, 'DD/MM/YYYY') " +
			"union all " +
			"select resource_id, allocation_end_date event_date, 'END' flag, -1*allocation_percentage from RP_PROJECT_RESOURCE_DETAIL " +
			"where resource_id in :resourceIdList and " +
			"allocation_end_date > to_date(:fromDate, 'DD/MM/YYYY') and allocation_end_date < to_date(:toDate, 'DD/MM/YYYY')" +
			") T order by T.resource_id, T.event_date", nativeQuery = true)
	List<Object[]> getResourceAllocationChangeEvents(@Param("fromDate") String fromDate, @Param("toDate") String toDate, @Param("resourceIdList") List<String> resourceIdList);
	
	//For Unit Testing
	/*@Query(value = "select resource_id, event_date, flag, allocation_percentage as allocation_change from " +
			"( select resource_id, allocation_start_date event_date, 'START' flag, allocation_percentage  from RP_PROJECT_RESOURCE_DETAIL " +
			"where resource_id in ('1','2','3') and " +
			"allocation_start_date > to_date('01/02/2014', 'DD/MM/YYYY') and allocation_start_date < to_date('30/07/2014', 'DD/MM/YYYY') " +
			"union all " +
			"select resource_id, allocation_end_date event_date, 'END' flag, -1*allocation_percentage from RP_PROJECT_RESOURCE_DETAIL " +
			"where resource_id in ('1','2','3') and " +
			"allocation_end_date > to_date('01/02/2014', 'DD/MM/YYYY') and allocation_end_date < to_date('30/07/2014', 'DD/MM/YYYY') " +
			") T order by T.resource_id, T.event_date", nativeQuery = true)
	List<Object[]> getResourceAllocationChangeEvents();
	*/
		
	
}
